﻿using System.Data;
using System.Threading;
using System.Threading.Tasks;

namespace Nardax.Data
{
    public interface IDatabase<TCommand, in TTransaction, TConnection>
    {
        /// <summary> 
        /// Creates a new instance of SqlCommand using the name of the stored procedure provided. The command 
        /// is returned with the command type set to StoredProcedure. 
        /// </summary>
        /// <param name="storedProcedureName">The name of the stored procedure.</param>
        /// <returns>A new instance of SqlCommand with the command type already set to StoredProcedure.</returns>
        TCommand CreateStoredProcedureCommand(string storedProcedureName);

        /// <summary> 
        /// Creates a new instance of SqlCommand using the command text provided.
        /// </summary>
        /// <param name="commandText">The SQL string that will be executed when the command is executed.</param>
        /// <returns>A new instance of SqlCommand with the command type and text configured.</returns>
        TCommand CreateCommand(string commandText);

        /// <summary> 
        /// Executes a non query command against the database. 
        /// </summary>
        /// <param name="command">The command to be executed.</param>
        /// <returns>Return value from the command.</returns>
        int ExecuteNonQuery(TCommand command);

        /// <summary> 
        /// An asynchronous version of <see cref="ExecuteNonQuery"/>. Executes a non query command against the database. 
        /// </summary>
        /// <param name="command">The command to be executed.</param>
        /// <returns>Return value from the command.</returns>
        Task<int> ExecuteNonQueryAsync(TCommand command);

        /// <summary> 
        /// An asynchronous version of <see cref="ExecuteNonQuery"/>. Executes a non query command against the database. 
        /// </summary>
        /// <param name="command">The command to be executed.</param>
        /// <param name="cancellationToken">The cancellation token that can be used to cancel the task.</param>
        /// <returns>Return value from the command.</returns>
        Task<int> ExecuteNonQueryAsync(TCommand command, CancellationToken cancellationToken);

        /// <summary> 
        /// Executes a non query command against the database. 
        /// </summary>
        /// <param name="command">The command to be executed.</param>
        /// <param name="transaction">The transaction that the command should be run against.</param>
        /// <returns>Return value from the command.</returns>
        /// <remarks>The connection that is used to execute the non query is the connection that is associated with the transaction.</remarks>
        int ExecuteNonQuery(TCommand command, TTransaction transaction);

        /// <summary> 
        /// An asynchronous version of <see cref="ExecuteNonQuery"/>. Executes a non query command against the database. 
        /// </summary>
        /// <param name="command">The command to be executed.</param>
        /// <param name="transaction">The transaction that the command should be run against.</param>
        /// <returns>Return value from the command.</returns>
        /// <remarks>The connection that is used to execute the non query is the connection that is associated with the transaction.</remarks>
        Task<int> ExecuteNonQueryAsync(TCommand command, TTransaction transaction);

        /// <summary> 
        /// An asynchronous version of <see cref="ExecuteNonQuery"/>. Executes a non query command against the database. 
        /// </summary>
        /// <param name="command">The command to be executed.</param>
        /// <param name="transaction">The transaction that the command should be run against.</param>
        /// <param name="cancellationToken">The cancellation token that can be used to cancel the task.</param>
        /// <returns>Return value from the command.</returns>
        /// <remarks>The connection that is used to execute the non query is the connection that is associated with the transaction.</remarks>
        Task<int> ExecuteNonQueryAsync(TCommand command, TTransaction transaction, CancellationToken cancellationToken);

        /// <summary> 
        /// Opens a reader on the database using the command passed in. 
        /// </summary>
        /// <param name="command">The command to be executed.</param>
        /// <returns>The open reader that can be used to get the rows returned by the command.</returns>
        IDataReader ExecuteReader(TCommand command);

        /// <summary> 
        /// An asynchronous version of <see cref="ExecuteReader"/>. Opens a reader on the database using the command passed in. 
        /// </summary>
        /// <param name="command">The command to be executed.</param>
        /// <returns>The open reader that can be used to get the rows returned by the command.</returns>
        Task<IDataReader> ExecuteReaderAsync(TCommand command);

        /// <summary> 
        /// An asynchronous version of <see cref="ExecuteReader"/>. Opens a reader on the database using the command passed in. 
        /// </summary>
        /// <param name="command">The command to be executed.</param>
        /// <param name="cancellationToken">The cancellation token that can be used to cancel the task.</param>
        /// <returns>The open reader that can be used to get the rows returned by the command.</returns>
        Task<IDataReader> ExecuteReaderAsync(TCommand command, CancellationToken cancellationToken);

        /// <summary> 
        /// Executes a scalar command against the database 
        /// </summary>
        /// <param name="command">The command to be executed.</param>
        /// <returns>The return result of the scalar command</returns>
        object ExecuteScalar(TCommand command);

        /// <summary> 
        /// An asynchronous version of <see cref="ExecuteScalar"/>. Executes a scalar command against the database 
        /// </summary>
        /// <param name="command">The command to be executed.</param>
        /// <returns>The return result of the scalar command</returns>
        Task<object> ExecuteScalarAsync(TCommand command);

        /// <summary> 
        /// An asynchronous version of <see cref="ExecuteScalar"/>. Executes a scalar command against the database 
        /// </summary>
        /// <param name="command">The command to be executed.</param>
        /// <param name="cancellationToken">The cancellation token that can be used to cancel the task.</param>
        /// <returns>The return result of the scalar command</returns>
        Task<object> ExecuteScalarAsync(TCommand command, CancellationToken cancellationToken);

        /// <summary> 
        /// Executes a scalar command against the database 
        /// </summary>
        /// <param name="command">The command to be executed.</param>
        /// <returns>The result of the scalar command with the specific type requested</returns>
        T ExecuteScalar<T>(TCommand command);

        /// <summary> 
        /// An asynchronous version of <see cref="ExecuteScalar"/>. Executes a scalar command against the database 
        /// </summary>
        /// <param name="command">The command to be executed.</param>
        /// <returns>The result of the scalar command with the specific type requested</returns>
        Task<T> ExecuteScalarAsync<T>(TCommand command);

        /// <summary> 
        /// An asynchronous version of <see cref="ExecuteScalar"/>. Executes a scalar command against the database 
        /// </summary>
        /// <param name="command">The command to be executed.</param>
        /// <param name="cancellationToken">The cancellation token that can be used to cancel the task.</param>
        /// <returns>The result of the scalar command with the specific type requested</returns>
        Task<T> ExecuteScalarAsync<T>(TCommand command, CancellationToken cancellationToken);

        /// <summary> 
        /// Executes a scalar command against the database 
        /// </summary>
        /// <param name="command">The command to be executed.</param>
        /// <returns>The result of the scalar command with the specific type requested</returns>
        T? ExecuteNullableScalar<T>(TCommand command) where T : struct;

        /// <summary> 
        /// An asynchronous version of <see cref="ExecuteScalar"/>. Executes a scalar command against the database 
        /// </summary>
        /// <param name="command">The command to be executed.</param>
        /// <returns>The result of the scalar command with the specific type requested</returns>
        Task<T?> ExecuteNullableScalarAsync<T>(TCommand command) where T : struct;

        /// <summary> 
        /// An asynchronous version of <see cref="ExecuteScalar"/>. Executes a scalar command against the database 
        /// </summary>
        /// <param name="command">The command to be executed.</param>
        /// <param name="cancellationToken">The cancellation token that can be used to cancel the task.</param>
        /// <returns>The result of the scalar command with the specific type requested</returns>
        Task<T?> ExecuteNullableScalarAsync<T>(TCommand command, CancellationToken cancellationToken) where T : struct;

        /// <summary>
        /// Creates an open connection to the sql database using the connection string that was passed in on constructiconnectionString
        /// default connection string from the configuration settings.
        /// </summary>
        /// <returns>An open connection to the database.</returns>
        /// <remarks>The connection that is returned is open and must be properly disposed of after use. If an error is thrown an attempt
        /// to close the connection will be made before the error is raised to the caller.</remarks>
        TConnection CreateOpenConnection();

        /// <summary>
        /// An asynchronous version of <see cref="CreateOpenConnection"/>. Creates an open connection to the sql database using the connection string that was passed in on constructiconnectionString
        /// default connection string from the configuration settings.
        /// </summary>
        /// <returns>An open connection to the database.</returns>
        /// <remarks>The connection that is returned is open and must be properly disposed of after use. If an error is thrown an attempt
        /// to close the connection will be made before the error is raised to the caller.</remarks>
        Task<TConnection> CreateOpenConnectionAsync();

        /// <summary>
        /// An asynchronous version of <see cref="CreateOpenConnection"/>. Creates an open connection to the sql database using the connection string that was passed in on constructiconnectionString
        /// default connection string from the configuration settings.
        /// </summary>
        /// <param name="cancellationToken">The cancellation token that can be used to cancel the task.</param>
        /// <returns>An open connection to the database.</returns>
        /// <remarks>The connection that is returned is open and must be properly disposed of after use. If an error is thrown an attempt
        /// to close the connection will be made before the error is raised to the caller.</remarks>
        Task<TConnection> CreateOpenConnectionAsync(CancellationToken cancellationToken);

        /// <summary>
        /// Adds or refreshes rows in a specified DataTable
        /// </summary>
        /// <param name="dataTable">The DataTable where the data should be put.</param>
        /// <param name="selectCommand">The command used to get the data from the database.</param>
        /// <returns>The number of rows successfully added to or refreshed in the System.Data.DataSet. This does not include rows 
        /// affected by statements that do not return rows.</returns>
        /// <remarks></remarks>
        int Fill(DataTable dataTable, TCommand selectCommand);

        /// <summary>
        /// Adds or refreshes rows in a specified DataSet
        /// </summary>
        /// <param name="dataSet">The DataSet where the data should be put.</param>
        /// <param name="selectCommand">The command used to get the data from the database.</param>
        /// <returns>The number of rows successfully added to or refreshed in the System.Data.DataSet. This does not include rows 
        /// affected by statements that do not return rows.</returns>
        /// <remarks></remarks>
        int Fill(DataSet dataSet, TCommand selectCommand);
    }
}
